﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.Coolzon.Core.Repositories.SQL
{
    public class SqlPrivilegeRepository : IPrivilegeRepository
    {
        public PagedList<Data.Privilege> GetPrivilegeList(DbContext context, int start, int limit)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [Privilege]) a where a.rownum >= @paramStart and a.rownum <= @paramEnd";
                        cmd.Parameters.Add(new SqlParameter("paramStart", start));
                        cmd.Parameters.Add(new SqlParameter("paramEnd", start + limit));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.Privilege> privilegeList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            privilegeList = new List<Data.Privilege>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.Privilege privilege = new Data.Privilege();
                                privilege.Id = new Guid(dr["Id"].ToString());
                                privilege.Code = dr["Code"].ToString();
                                privilege.Name = dr["Name"].ToString();
                                privilege.Type = dr["Type"].ToString();
                                privilegeList.Add(privilege);
                            }
                            return new PagedList<Data.Privilege>(privilegeList, start, limit, privilegeList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CreatePrivilege(DbContext context, Data.Privilege privilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "insert into [Privilege](Id, Code, Name, Type) values(@paramId, @paramCode, @paramName, @paramType)";
                    cmd.Parameters.Add(new SqlParameter("paramId", privilege.Id));
                    cmd.Parameters.Add(new SqlParameter("paramCode", privilege.Code));
                    cmd.Parameters.Add(new SqlParameter("paramName", privilege.Name));
                    cmd.Parameters.Add(new SqlParameter("paramType", privilege.Type));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool RemovePrivilege(DbContext context, Data.Privilege privilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from [Privilege] where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", privilege.Id));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdatePrivilege(DbContext context, Data.Privilege privilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "update [Privilege] set Code = @paramCode, Name = @paramName, Type = @paramType where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", privilege.Id));
                    cmd.Parameters.Add(new SqlParameter("paramCode", privilege.Code));
                    cmd.Parameters.Add(new SqlParameter("paramName", privilege.Name));
                    cmd.Parameters.Add(new SqlParameter("paramType", privilege.Type));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Data.Privilege GetPrivilege(DbContext context, Guid privilegeId)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from [Privilege] where Id = @paramId";
                        cmd.Parameters.Add(new SqlParameter("paramId", privilegeId));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            Data.Privilege privilege = new Data.Privilege();
                            privilege.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                            privilege.Code = ds.Tables[0].Rows[0]["Code"].ToString();
                            privilege.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                            privilege.Type = ds.Tables[0].Rows[0]["Type"].ToString();
                            return privilege;
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
